-- @owner: songjing20
-- @date: 2022-6-29
-- @testpoint: 大数据量下，验证limit值和offset值是否准确
-- @modified by: xiaqi14 2024/12/9 研发代码变更,修改时间精度

--step1:创建ustore表样;expect:创建表样成功
drop table if exists t_limit_clause_0023;
create table t_limit_clause_0023(
COL_1 int, 
COL_2 decimal,
COL_3 bigint,
COL_4 char(30),
COL_5 real,
COL_6 numeric,
COL_7 int,
COL_8 TIMESTAMP,
COL_9 clob,
COL_10 number(12,2),
COL_11 decimal(12,2),
COL_12 varchar2(50),
COL_13 varchar(30),
COL_14 numeric(12,6),
COL_15 date,
constraint pk_limit_006_id primary key(COL_1)
) WITH (STORAGE_TYPE=USTORE);

--step2:给表样插入数据;expect:插入数据成功
drop sequence if exists seq_limit_clause_0023;
create sequence seq_limit_clause_0023 increment by 1 start with 2000000;
begin
 for i in 1..1000000 loop
      insert into t_limit_clause_0023 values(
      seq_limit_clause_0023.nextval,
      3.1415926+seq_limit_clause_0023.nextval,
      i,
      lpad('abc','30','@'),
      3.1415926+seq_limit_clause_0023.nextval,
      i/4,
      i,
      to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FF'),
      lpad('abc','5000','a@123&^%djgk'),
      3.14+i,
      i+445.255,
      rpad('abc','30','&'),
      lpad('abc','30','&'),
      125563.141592,
      to_date('2019-03-01 14:58:54','YYYY-MM-DD HH24:MI:SS')
   );
 end loop;
end;
/
analyze t_limit_clause_0023;

--step3:跳过999900条数据取100条数据;expect:成功
select COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 from t_limit_clause_0023
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 limit 999900,100;

select COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 from t_limit_clause_0023
order by COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 limit 100 OFFSET 999900;

--step4:跳过9999条数据取8000条数据;expect:成功
with tmp1 as (select COL_1,COL_2,COL_3,COL_4,COL_5,COL_6,COL_7,COL_8,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15 from t_limit_clause_0023
order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14 OFFSET 9999 limit 10000) select count(*) from tmp1;

--step3:清理环境;expect:成功
drop table if exists t_limit_clause_0023 cascade;
drop sequence if exists seq_limit_clause_0023;
